# db_utils.py
import pymysql
from scrapy.utils.project import get_project_settings


class DBUtils:
    def __init__(self):
        settings = get_project_settings()
        self.conn = pymysql.connect(
            host=settings.get('MYSQL_HOST'),
            port=settings.get('MYSQL_PORT'),
            user=settings.get('MYSQL_USER'),
            password=settings.get('MYSQL_PASSWORD'),
            db=settings.get('MYSQL_DATABASE'),
            charset='utf8mb4'
        )
    def get_unscore_matches(self, limit=20):
        """获取未爬取的比赛"""
        with self.conn.cursor(pymysql.cursors.DictCursor) as cursor:
            sql = "SELECT * FROM spider_lottery_football_match_sporttery WHERE sections_no999 is null or sections_no999='' ORDER BY match_id LIMIT %s"
            cursor.execute(sql, (limit,))
            return cursor.fetchall()

    def get_unscraped_matches(self, limit=20):
        """获取未爬取的比赛"""
        with self.conn.cursor(pymysql.cursors.DictCursor) as cursor:
            sql = "SELECT * FROM spider_lottery_football_match_sporttery WHERE sell_status<>4 ORDER BY match_id LIMIT %s"
            cursor.execute(sql, (limit,))
            return cursor.fetchall()

    def get_un_match_result_list_matches(self, limit=20):
        """获取未爬取的比赛"""
        with self.conn.cursor(pymysql.cursors.DictCursor) as cursor:
            sql = "SELECT * FROM spider_lottery_football_match_sporttery WHERE match_result_list is null or match_result_list='' ORDER BY match_id LIMIT %s"
            cursor.execute(sql, (limit,))
            return cursor.fetchall()

    def update_match_result(self, match_id, result, odds):
        """更新比赛结果"""
        with self.conn.cursor() as cursor:
            sql = """UPDATE match_info 
                    SET is_crawled = 1, result = %s, odds = %s 
                    WHERE match_id = %s"""
            cursor.execute(sql, (result, odds, match_id))
            self.conn.commit()

    def close(self):
        self.conn.close()